package com.apestech.framework.sql;

import com.apestech.formwork.condition.ConditionUtil;
import com.apestech.formwork.xml.XmlQuery;
import com.apestech.framework.db.SqlResultSet;
import com.apestech.framework.db.SqlSession;
import com.apestech.framework.db.callback.QueryCallBackHandler;
import com.apestech.framework.db.callback.SaveTableCallBackHandler;
import com.apestech.framework.dictionary.modul.Table;
import com.apestech.framework.json.JSONArray;
import com.apestech.framework.json.JSONObject;
import com.apestech.framework.util.StringUtil;
import com.apestech.framework.util.callback.SpiltStrCallback;
import com.apestech.framework.xml.condition.modul.Report;
import com.apestech.framework.xml.plugin.XmlPaste;

import java.sql.SQLException;
import java.util.ArrayList;

/**
 * @author hup
 * @create 2019-04-16-9:34
 */
public class ReportSql {
    public class Sql {
        private String efield;   //v1=#{v1},v2=#{v2}
        private String dfield;   //v1,v2
        private String vfield;   //#{v1},#{v2}
        private String vifield;  //key #{v1},#{v2}
        private String difield;  //key v1,v2
        private String kfield;   //key where
        private String insert;
        private String update;

        public Sql(String efield, String dfield, String vfield, String vifield, String difield, String kfield){
            this.efield=efield;
            this.dfield=dfield;
            this.vfield=vfield;
            this.vifield=vifield;
            this.difield=difield;
            this.kfield=kfield;
        }

        public String getInsert(String tableName){
            if(insert==null){
                String col=difield;
                if(!"".equals(difield) && !"".equals(dfield)) {
                    col=col+",";
                }
                col=col+dfield;

                String val=vifield;
                if(!"".equals(vifield) && !"".equals(vfield)) {
                    val=val+",";
                }
                val=val+vfield;
                insert="INSERT INTO "+tableName+"("+col+") VALUES ("+val+")";
            }
            return insert;
        }
        public String getUpdate(String tableName){
            if(update==null){
                update="UPDATE "+tableName+" SET "+efield+" WHERE 1=1 AND "+kfield;
            }
            return update;
        }
    }

    private SqlSession session;
    private Report report;
    private XmlQuery xmlQuery;

    public ReportSql(SqlSession session){
        this.session=session;
    }

    public void execute(String reportId, JSONObject input) throws Exception {
        report= ConditionUtil.getReport(reportId, session);
        if(report==null){
            XmlPaste xml;
            if(xmlQuery==null){
                xml=new XmlPaste(reportId,"report",session);
                xmlQuery=xml.getXmlQuery();
            } else {
                xml=new XmlPaste(reportId, xmlQuery);
            }
            xml.expain();
            report=xml.getReport();
        }
        if(report==null) return;
        String tableName=report.getTableName();
        if(tableName==null) report.getTable().getMasterTableId();
        if(tableName==null) return;
        Table table=session.getDictTable(tableName);
        if(report.getEmpty()!=null){
            htw(tableName, input);
        }
        execUpdate(report.getIniUpdate(), input);
        ArrayList<Report.SumColumn> sumcol=report.getSumColumn();
        int sumclen=sumcol.size();
        if(sumclen>0){
            for(int i=0; i<sumclen; i++){
                Report.SumColumn col=sumcol.get(i);
                final Sql[] sqls = {null};
                input.put("autoCompletion","0");
                session.getForResultSet().sql(col.getSql(), input, new QueryCallBackHandler() {
                    @Override
                    public void getData(SqlResultSet rs, JSONArray rslist) throws Exception {
                        while(rs.next()){
                            JSONObject map=new JSONObject();
                            map.putAll(input);
                            map.putAll(rs.toRowJson());
                            map=new ReportSqlSave(report, session).updateSql(map);
                            if(sqls[0] ==null) {
                                sqls[0] =getSaveSql(table, map, col.getField());
                            }
                            int t=session.getForUpdate().update(sqls[0].getUpdate(tableName), map);
                            if(t==0) {
                                map=new ReportSqlSave(report, session).insertSql(map);
                                sqls[0] =getSaveSql(table, map, col.getField());
                                session.getForUpdate().update(sqls[0].getInsert(tableName), map);
                            }
                        }
                    }
                });
            }
        }
        execUpdate(report.getFinalUpdate(), input);
    }

    private void execUpdate(ArrayList<Report.FinalUpdate> updates, JSONObject input) throws Exception {
        int ulen=updates.size();
        for(int i=0; i<ulen; i++){
            Report.FinalUpdate u=updates.get(i);
            switch (u.getType()){
                case "insert":{
                    session.getForUpdate().insertTable(u.getTableName(), input);
                    break;
                }
                case "update":{
                    if(u.getSql()!=null){
                        session.getForUpdate().update(u.getSql(), input);
                    } else {
                        session.getForUpdate().updateTable(u.getTableName(), input);
                    }
                    break;
                }
                case "delete":{
                    session.getForUpdate().deleteTable(u.getTableName(), input);
                    break;
                }
                case "save":{
                    session.getForUpdate().saveTable(u.getTableName(), input);
                    break;
                }
            }
        }
    }

    private Sql getSaveSql(Table table, JSONObject map, String field) throws Exception {
        String col=table.getColumn();
        final String[] efield = {""};  //v1=#{v1} and v2=#{v2}
        final String[] dfield = {""};  //v1,v2
        final String[] vfield = {""};  //#{v1},#{v2}
        final String[] vifield = {""};  //v1=#{v1} and v2=#{v2}
        final String[] difield = {""};  //v1,v2
        final String[] kfield = {""};
        String key="";
        if(table.getPkKey()!=null){
            key=table.getPkKey().getPkColumn();
        }
        String finalKey = key;
        //String sysfield=map.getString("sys_upfield");
        //col=col+","+sysfield;
        StringUtil.execSplitStr(col, ",", new SpiltStrCallback() {
            @Override
            public void exec(String id) throws Exception {
                if(map.get(id)!=null){
                    if((";"+ finalKey +";").indexOf(";"+id+";")<0){
                        if(!"".equals(efield[0])) {
                            efield[0] = efield[0]+",";
                        }
                        if(field.equals(id)){
                            efield[0] = efield[0] +id+"=#{"+id+"} + "+"NVL("+id+",0)";
                        } else {
                            efield[0] = efield[0] +id+"=#{"+id+"}";
                        }
                        if(!"".equals(dfield[0])){
                            dfield[0] = dfield[0]+",";
                        }
                        dfield[0] = dfield[0] +id;
                        if(!"".equals(vfield[0])){
                            vfield[0] = vfield[0]+",";
                        }
                        vfield[0] = vfield[0] +"#{"+id+"}";
                    } else {
                        if(!"".equals(difield[0])){
                            difield[0] = difield[0]+",";
                        }
                        difield[0] = difield[0] +id;
                        if(!"".equals(vifield[0])){
                            vifield[0] = vifield[0]+",";
                        }
                        vifield[0] = vifield[0] +"#{"+id+"}";
                        if(!"".equals(kfield[0])) {
                            kfield[0] = kfield[0]+" AND ";
                        }
                        kfield[0] = kfield[0] +id+"=#{"+id+"}";
                    }
                }
            }
        });
        return new Sql(efield[0], dfield[0], vfield[0], vifield[0], difield[0], kfield[0]);
    }

    private void htw(String tableName, JSONObject input) throws Exception {
        execEmpty(tableName, input);
        int line=report.line();
        for(int i=0; i<line; i++){
            ArrayList<String> list=report.getColList(i);
            int len=list.size();
            for(int j=0; j<len; j++){
                String id=list.get(j);
                Report.Column col=report.getColumn(i, id);
                String sql=col.getSql();
                if(sql!=null) sql=sql.trim();
                if(col.getModul()!=null){
                    if(!sql.equals("")) sql=report.getModulSql(col.getModul()).replace("#WHERE#", " AND ("+sql+")");
                    else sql=col.getModul();
                }
                input.put("nb_alldata","1");
                JSONArray qlist=session.getForJSON().query(sql, input);
                JSONObject m=new JSONObject();
                m.put("table_param_field","1");
                session.getForUpdate().saveTables(tableName, qlist, m, new ReportSqlSave(report, session));
            }
        }
    }

    private void execEmpty(String tableName, JSONObject input) throws Exception {
        String empty=report.getEmpty();
        if(empty==null) return;
        JSONArray qlist=session.getForJSON().query(empty, input);
        input.put("table_param_field","1");
        session.getForUpdate().saveTables(tableName, qlist, input, null);
    }

}
